Node / ReticulumProjects / MeshChatX.git / files / docs / agents / skills / database-migrations-backups / SKILL.md

Displaying Raw • View rendered • Download

Tc9d1d9# Skill: database-migrations-backups

Bump schema versions correctly, keep backups and snapshots safe, and never conflate identity-key restore with full database zip restore.

Tc9d1d9## When to use

Tff7b72- Changing SQLite schema or migrations

Tff7b72- Touching backup, snapshot, restore, or crash-recovery paths

Tff7b72- Adding tables / columns used by conversation or settings features

Tc9d1d9## Schema rules

Tff7b72- Engine is SQLite with explicit SQL. No ORM.

Tff7b72- Bump Ta5d6ff`LATEST_VERSION` in Ta5d6ff`meshchatx/src/backend/database/schema.py` and add a migration path.

Tff7b72- Test upgrade from an older version when the change is non-trivial.

Tff7b72- Heavy data backfills in migrations should skip when the target table is empty. Fresh Ta5d6ff`Database Initialization` benches create empty DBs and still run every migration step.

Tc9d1d9### Expand-only policy (release N)

Tff7b72- Prefer Ta5d6ff`CREATE TABLE`, Ta5d6ff`ADD COLUMN` with NULL or default, and indexes.

Tff7b72- Avoid Ta5d6ff`DROP TABLE`, Ta5d6ff`DROP COLUMN`, table renames, and destructive Ta5d6ff`DELETE FROM` in the same release that new app code depends on.

Tff7b72- Defer drops and renames to **N+2** (two-release lag).

Tff7b72- When Ta5d6ff`LATEST_VERSION` bumps, note the schema change in Ta5d6ff`CHANGELOG.md`.

Tff7b72- CI runs Ta5d6ff`scripts/ci/check_schema_migrations.py` on Ta5d6ff`schema.py` changes. Rare destructive steps need Ta5d6ff`# migration-safety: allow-destructive` on the same line.

Tc9d1d9## Backup and snapshot rules

Tff7b72- Backups skip Ta5d6ff`database-backups/` and Ta5d6ff`snapshots/` so a new zip does not nest itself (Ta5d6ff`BACKUP_SKIP_DIR_NAMES`).

Tff7b72- Suspicious shrink writes Ta5d6ff`backup-SUSPICIOUS-*.zip` and skips rotation. Do not treat that as a normal backup.

Tff7b72- Checkpoint WAL before zip snapshots when the live DB is open.

Tff7b72- Before applying schema upgrades (Ta5d6ff`current_version` below Ta5d6ff`LATEST_VERSION`), write Ta5d6ff`backup-pre-migrate-v*-to-v*.zip` under Ta5d6ff`database-backups/` unless Ta5d6ff`MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`. Migration aborts if that backup fails.

Tff7b72- After migrate, Ta5d6ff`PRAGMA quick_check` and Ta5d6ff`SELECT 1` must pass before the version row is updated. Failures log Ta5d6ff`schema_migration ... status=failed` and block startup.

Tff7b72- Prune older Ta5d6ff`backup-pre-migrate-*.zip` files, keeping five by default (Ta5d6ff`MESHCHAT_PRE_MIGRATE_BACKUP_KEEP`, Ta5d6ff`0` disables pruning).

Tff7b72- Ta5d6ff`database_version` greater than Ta5d6ff`LATEST_VERSION` raises Ta5d6ff`DatabaseTooNewError` at startup.

Tff7b72- Worker-thread connections must share Ta5d6ff`DatabaseProvider` pragmas (see Ta5d6ff`landlock-sqlite`).

Tff7b72- One storage directory per running instance: Ta5d6ff`StorageLock` serializes migration and runtime (do not run two replicas on one Ta5d6ff`/config` volume).

Tc9d1d9## Fixture workflow

When Ta5d6ff`LATEST_VERSION` changes:

Ta5d6ff```Ta5d6ffbash

task schema-fixtures

Ta5d6ff```

This writes Ta5d6ff`tests/backend/fixtures/schema_versions/schema_v{N}.db` for latest, N-1, and N-2 plus Ta5d6ff`manifest.json` (manifest is committed, Ta5d6ff`*.db` files are gitignored and generated before backend tests).

Tc9d1d9## Two restore operations

Private key only vs full history/settings/identity tree restore are different operations with different APIs. Full table: Ta5d6ff`docs/agents/overview.md` (Identity key restore vs database restore section).

Details for pickers and tutorial copy: Ta5d6ff`identity-restore`.

Tc9d1d9## Key files

Tff7b72- Ta5d6ff`meshchatx/src/backend/database/schema.py`

Tff7b72- Ta5d6ff`meshchatx/src/backend/database/__init__.py`

Tff7b72- Ta5d6ff`meshchatx/meshchat.py` (backup / restore routes, Ta5d6ff`prepare_for_database_restore`)

Tff7b72- Ta5d6ff`electron/offlineRecovery.js`

Tff7b72- Ta5d6ff`scripts/ci/check_schema_migrations.py`

Tff7b72- Ta5d6ff`scripts/ci/schema_fixture_generate.py`

Tc9d1d9## Verification

Ta5d6ff```Ta5d6ffbash

uv run python scripts/ci/check_schema_migrations.py

uv run pytest tests/backend/test_database_snapshots.py tests/backend/test_schema_migration_upgrade.py tests/backend/test_schema_migration_matrix.py -q --tbTff7b72=short

Ta5d6ff```